/* ******************************************************************************************************* */
/* ******************* Constructs a dataset for post-vote net buying by mutual funds *************I*********/
/* ******************************************************************************************************* */

/*** mutual fund trades;*/
proc sort data = temp.s12_holdings (keep=wficn rdate fdate) nodupkey out = wficn; by wficn rdate fdate; 

data wficn; set wficn; by wficn rdate;
  if first.rdate;
  rqdate = intnx('quarter', rdate, 0, 'end');
  format rqdate date9.;
  if wficn ne .;

proc sort; by wficn rqdate rdate; 

data wficn; set wficn; by wficn rqdate rdate; 
  if last.rqdate;
run;

proc sql;
  create table wficn as
  select a.*, b.rdate as rdate_last
  from wficn as a left join wficn as b
  on a.wficn = b.wficn and intck('month', b.rdate, a.rdate) = 3;
quit;

proc sql;
  create table wficn as
  select a.*, b.rdate as rdate_next
  from wficn as a left join wficn as b
  on a.wficn = b.wficn and intck('month', a.rdate, b.rdate) = 3;
quit;

proc sql;
  create table mf as
  select a.*, b.rqdate, b.rdate_last, b.rdate_next
  from temp.s12_holdings (keep=permno wt_adj rdate wficn pwt market_value fdate) as a, wficn as b
  where a.wficn = b.wficn and b.rdate = a.rdate and a.fdate = b.fdate; 
quit;

proc sql;
  create table mf as
  select a.*, b.wt_adj as wt_last, b.pwt as pwt_last, b.market_value as market_value_last
  from mf as a left join mf as b
  on a.wficn = b.wficn and a.permno = b.permno and a.rdate_last = b.rdate;
quit;

proc sql;
  create table mf as
  select a.*, b.wt_adj as wt_next, b.pwt as pwt_next, b.market_value as market_value_next
  from mf as a left join mf as b
  on a.wficn = b.wficn and a.permno = b.permno and a.rdate_next = b.rdate; 
quit;

data exits; set mf;
  if wt_adj ne . and wt_next = . and rdate_next ne .; *** identify complete exits;
  rdate_last = rdate; rdate = rdate_next; wt_last = wt_adj; wt_adj = 0; pwt_last = pwt; pwt = 0; market_value_last = market_value; market_value = 0;
  rqdate = intnx('quarter', rdate, 0, 'end');
  drop rdate_next wt_next market_value_next;

data temp.s12_trades; set mf exits;
  if pwt = . then delete;
  wt = wt_adj;
  if wt_last = . and rdate_last ne . then do; wt_last = 0; pwt_last = 0; market_value_last = 0; end; **** entry trades;
  netbuy = wt-wt_last;
  keep permno wficn rqdate rdate rdate_last netbuy wt wt_last pwt pwt_last market_value_last market_value;
run;

/*** mutual fund trades;*/
proc sort data = temp.holdings nodupkey out = crsp_cl_grp (keep=crsp_cl_grp report_dt); by crsp_cl_grp report_dt; 
  where permno ne .;

data crsp_cl_grp; set crsp_cl_grp;
  rqdate = intnx('quarter', report_dt, 0, 'end');
  format rqdate date9.;
  if crsp_cl_grp ne .;

proc sort; by crsp_cl_grp rqdate report_dt; 

data crsp_cl_grp; set crsp_cl_grp; by crsp_cl_grp rqdate report_dt; 
  if last.rqdate;

proc sql;
  create table crsp_cl_grp as
  select a.*, b.report_dt as report_dt_last
  from crsp_cl_grp as a left join crsp_cl_grp as b
  on a.crsp_cl_grp = b.crsp_cl_grp and intck('month', b.report_dt, a.report_dt) = 3;
quit;

proc sql;
  create table crsp_cl_grp as
  select a.*, b.report_dt as report_dt_next
  from crsp_cl_grp as a left join crsp_cl_grp as b
  on a.crsp_cl_grp = b.crsp_cl_grp and intck('month', a.report_dt, b.report_dt) = 3;
quit;

proc sql;
  create table mf_ as
  select a.*, b.rqdate, b.report_dt_last, b.report_dt_next
  from temp.holdings (keep=permno wt report_dt crsp_cl_grp pwt market_value) as a, crsp_cl_grp as b
  where a.crsp_cl_grp = b.crsp_cl_grp and b.report_dt = a.report_dt; 
quit;

proc sql;
  create table mf_ as
  select a.*, b.wt as wt_last, b.pwt as pwt_last, b.market_value as market_value_last
  from mf_ as a left join mf_ as b
  on a.crsp_cl_grp = b.crsp_cl_grp and a.permno = b.permno and a.report_dt_last = b.report_dt;
quit;

proc sql;
  create table mf_ as
  select a.*, b.wt as wt_next, b.pwt as pwt_next, b.market_value as market_value_next
  from mf_ as a left join mf_ as b
  on a.crsp_cl_grp = b.crsp_cl_grp and a.permno = b.permno and a.report_dt_next = b.report_dt; *** identify entry buys: if permno2 = . then entry = 1;
quit;

data exits_; set mf_;
  if wt_next = . and report_dt_next ne . and wt ne .;
  report_dt_last = report_dt; report_dt = report_dt_next; wt_last = wt; wt = 0; pwt_last = pwt; pwt = 0; market_value_last = market_value; market_value = 0;
  rqdate = intnx('quarter', report_dt, 0, 'end');

data temp.trades; set mf_ (in=a) exits_ (in=b);
  if pwt = . then delete;
  if wt_last = . and report_dt_last ne . then do; wt_last = 0; pwt_last = 0; market_value_last = 0; end; **** entry trades;
  netbuy = wt-wt_last;
  rdate = report_dt;
  rdate_last = report_dt_last;
  keep permno crsp_cl_grp rqdate rdate netbuy wt wt_last pwt pwt_last market_value market_value_last rdate_last;

proc sort; by permno crsp_cl_grp rqdate; 

proc means data = temp.trades n mean median p1 p99 min max;
  var netbuy pwt pwt_last; 
run;


***** LINK TO S12 holdings;
proc sql;
  create table proposal_trades as
  select a.*, b.rdate, b.rdate_last, b.netbuy
  from temp.proposal_funds as a left join temp.s12_trades as b
  on a.wficn = b.wficn and intck('quarter', a.meetingdate, b.rdate_last) in (0,1) and intck('quarter', a.meetingdate, b.rdate) in (1,2) and a.permno = b.permno;
quit; 
* temp.proposal_funds is from 12Proposal_level.sas;

proc sort data = proposal_trades; by permno meetingdate wficn crsp_cl_grp rdate_last rdate;

data proposal_trades; set proposal_trades; by permno meetingdate wficn crsp_cl_grp rdate_last rdate;
  if first.crsp_cl_grp;

data proposal_trades1a; set proposal_trades;
  if netbuy ne .; 

data proposal_trades1b; set proposal_trades;
  if netbuy = .; 
  drop rdate rdate_last netbuy;

 proc sql;
  create table proposal_trades1b as
  select a.*, b.rdate, b.rdate_last, b.netbuy
  from proposal_trades1b as a, temp.trades as b
  where a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', a.meetingdate, b.rdate_last) in (0,1) and intck('quarter', a.meetingdate, b.rdate) in (1,2) and a.permno = b.permno;
quit;run; * temp.holdings is from 02Holdings.sas;

proc sort data = proposal_trades1b; by permno meetingdate wficn crsp_cl_grp rdate_last rdate;

data proposal_trades1b; set proposal_trades1b; by permno meetingdate wficn crsp_cl_grp rdate_last rdate;
  if first.crsp_cl_grp;

data proposal_posttrade; set proposal_trades1a proposal_trades1b;
  if q_vote_alpha = 4 then informed = 1; else if q_vote_alpha ne . then informed = 0;

proc sort nodupkey; by permno meetingdate wficn crsp_cl_grp;

proc means data = proposal_posttrade noprint; by permno meetingdate;
  var wt; 
  output out = mfo (drop=_type_ _freq_) sum = mfo n = num_mf;

data proposal_posttrade; merge proposal_posttrade mfo; by permno meetingdate;
  if netbuy ne . and wficn ne . and informed ne .;
run;

proc sql;
  create table proposal_posttrade as 
  select *
  from proposal_posttrade as a left join temp.cst_matched as b
  on a.permno = b.permno and year(a.meetingdate) = year(b.meetingdate) and month(a.meetingdate) = month(b.meetingdate) and day(a.meetingdate) = day(b.meetingdate);
quit; run; * temp.cst_matched is from 07CST_CRSP_Annual.sas;

proc sql;
  create table proposal_posttrade as
  select a.*, b.*
  from proposal_posttrade as a left join temp.meetings_car_permno as b
  on a.permno = b.permno and year(a.meetingdate) = year(b.meetingdate) and month(a.meetingdate) = month(b.meetingdate) and day(a.meetingdate) = day(b.meetingdate);
quit;

%winsor(dsetin=proposal_posttrade, dsetout=proposal_posttrade, byvar=none, vars=netbuy size mb car_pr252d_m leverage_b capex sp500 mfo nanalyst, type=winsor, pctl=.5 99.5); 

data proposal_posttrade; set proposal_posttrade;
  WinVote_informed = WinVote*informed;

  if issformgmt = 1 then iss_win = mgmt_win; else if issformgmt = 0 then iss_win = 1-mgmt_win;
  wficn_year = wficn*100000+year(meetingdate);
  permno_year = permno*100000+year(meetingdate);
  wficn_permno = wficn*100000+permno;
  wficn_agenda_year = put(wficn,6.)||issagendaitemid||put(year,4.);

  log_nanalyst = log(1+nanalyst);
run;

proc means data = proposal_posttrade n mean median min max t; 
  var netbuy;

proc sort; by itemonagendaid wficn_year permno meetingdate mgmt_sponsored mgmt_win Itemdesc issAgendaItemId mgmtFor issForMgmt issue margin year;
run;


proc export data= proposal_posttrade 
            outfile= "D:\Dropbox\InformedVoting\proposal_posttrade.dta" 
            dbms=stata replace;
run;
